﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using System.Data;
using System.IO;
using YYNY.Tools.Dao;
using NPOI.SS.Util;
using NPOI.SS.Converter;
using NPOI.XSSF.Util;
using System.Drawing;
using System.Web.Mvc;
using YYNY.Controllers;

namespace YYNY.Tools
{
    public class ExcelEstUserCS
    {
        BaseDao dao = new BaseDao();
        public XSSFWorkbook EstUserExport(string PreEstId, string ProjectName, string Name)
        {

            string temp = "";
            int currRow = 0;
            int tempRow = 0;
            double temp1 = 0;
            int i1 = 0;
            string fileName = "土建核价单 " + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-ff");
            //创建07版Excel文件对象
            XSSFWorkbook book = new XSSFWorkbook();
            ISheet sheet1 = book.CreateSheet("汇总表");
            ISheet sheet2 = book.CreateSheet("安装成本");
            ISheet sheet3 = book.CreateSheet("土建成本");
            ISheet sheet4 = book.CreateSheet("设备成本");
            ISheet sheet5 = book.CreateSheet("材料成本");
            ISheet sheet6 = book.CreateSheet("试验费明细");
            ISheet sheet7 = book.CreateSheet("协调费明细");
            ISheet sheet8 = book.CreateSheet("土建核价单");

            XSSFCellStyle fCellStyle = (XSSFCellStyle)book.CreateCellStyle();
            XSSFFont ffont = (XSSFFont)book.CreateFont();
            ffont.FontHeight = 220;
            ffont.FontName = "宋体";
            ffont.Color = new XSSFColor(Color.Black).Indexed;
            fCellStyle.SetFont(ffont);
            fCellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直对齐
            fCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平对齐
            IDataFormat format = book.CreateDataFormat();
            fCellStyle.DataFormat = format.GetFormat("#,##0.00 ");//这是设置为3位数货币样式,#表示空位保留
            fCellStyle.BorderLeft = BorderStyle.Thin;
            fCellStyle.BorderRight = BorderStyle.Thin;
            fCellStyle.BorderTop = BorderStyle.Thin;
            fCellStyle.BorderBottom = BorderStyle.Thin;

            string sql = "";
            ///////////////////////////////////////////////////////////////////
            //汇总表部分      
            sheet1.SetColumnWidth(0, 5000);
            sheet1.SetColumnWidth(1, 5000);
            sheet1.SetColumnWidth(2, 5000);
            sheet1.SetColumnWidth(3, 5000);
            sheet1.SetColumnWidth(4, 5000);
            sheet1.SetColumnWidth(5, 5000);
            sheet1.SetColumnWidth(6, 5000);
            sheet1.SetColumnWidth(7, 5000);

            var EstimateCtl = DependencyResolver.Current.GetService<EstimateController>();
            var MapCS = EstimateCtl.GetPEUserSummary(PreEstId, "用户工程前期测算");
            sql = "select * from o_PreEstUserCS where PreEstId=" + PreEstId;
            Dictionary<string, object> map2 = new Dictionary<string, object>();
            string[] field = new string[] { "XMMC", "YDXZ", "SSQY", "JZMJ", "RL", "WXCD", "HXNBJJE", "Memo_WXTJ","Memo_WXSB","Memo_WXCL","Memo_KBSSB","Memo_KBSCL","Memo_GYSB","Memo_GYCL",
                "Memo_ZYSB", "Memo_ZYCL", "Memo_ZTAZ", "Memo_ZTTJSGF", "Memo_ZTSYTSF", "YZYQ_CF", "Memo_YZYQCF", "YZYQ_YHYB","Memo_YZYQYHYB","YZYQ_LLLJ","Memo_YZYQLLLJ","YZYQ_CDZ","Memo_YZYQCDZ","DJHTJE","Memo_DJHTJE",
                    "SJF","Memo_SJF","JLF","Memo_JLF","DJGLF","Memo_DJGLF","SJ","Memo_SJ","ZJCB","Memo_ZJCB","GSGLF","Memo_GSGLF","Memo_YWF","LRL","Memo_LRL","HTJE","PreEstUserId" };
            map2 = dao.GetList(sql, field)[0];
            MapCS = MapCS.Concat(map2).ToDictionary(k => k.Key, v => v.Value);

            var MapBJ = EstimateCtl.GetPEUserSummary(MapCS["PreEstUserId"].ToString(), "用户工程");
            sql = "select * from o_PreEstUser where PreEstId=" + MapCS["PreEstUserId"].ToString();
            Dictionary<string, object> map4 = new Dictionary<string, object>();
            string[] field2 = new string[] { "XMMC", "YDXZ", "SSQY", "JZMJ", "RL", "WXCD", "HXNBJJE", "Memo_WXTJ","Memo_WXSB","Memo_WXCL","Memo_KBSSB","Memo_KBSCL","Memo_GYSB","Memo_GYCL",
                "Memo_ZYSB", "Memo_ZYCL", "Memo_ZTAZ", "Memo_ZTTJSGF", "Memo_ZTSYTSF", "YZYQ_CF", "Memo_YZYQCF", "YZYQ_YHYB","Memo_YZYQYHYB","YZYQ_LLLJ","Memo_YZYQLLLJ","YZYQ_CDZ","Memo_YZYQCDZ","DJHTJE","Memo_DJHTJE",
                    "SJF","Memo_SJF","JLF","Memo_JLF","DJGLF","Memo_DJGLF","SJ","Memo_SJ","ZJCB","Memo_ZJCB","GSGLF","Memo_GSGLF","Memo_YWF","LRL","Memo_LRL" };
            map4 = dao.GetList(sql, field2)[0];
            MapBJ = MapBJ.Concat(map4).ToDictionary(k => k.Key, v => v.Value);

            //以下操作把数值为空的项目变为0
            var tempmap = new Dictionary<string, object>();
            foreach (string key in MapCS.Keys)
            {
                tempmap.Add(key, MapCS[key]);
            }
            foreach (string key in tempmap.Keys)
            {
                if (!key.Contains("Memo"))
                {
                    if (tempmap[key].ToString() == "")
                    {
                        MapCS[key] = 0;
                    }
                }

            }
            tempmap.Clear();
            foreach (string key in MapBJ.Keys)
            {
                tempmap.Add(key, MapBJ[key]);
            }
            foreach (string key in tempmap.Keys)
            {
                if (!key.Contains("Memo"))
                {
                    if (tempmap[key].ToString() == "")
                    {
                        MapBJ[key] = 0;
                    }
                }

            }

            double tempNum1 = 0;
            double tempNum2 = 0;
            double BJXJ = 0;
            double BJHJ = 0;
            double CSXJ = 0;
            double CSHJ = 0;
            double HTJE = double.Parse(MapCS["HTJE"].ToString());

            BJXJ = double.Parse(MapBJ["YZYQ_CDZ"].ToString()) + double.Parse(MapBJ["YZYQ_LLLJ"].ToString()) +
                double.Parse(MapBJ["YZYQ_YHYB"].ToString()) + double.Parse(MapBJ["YZYQ_CF"].ToString()) +
                double.Parse(MapBJ["TestFee"].ToString()) + double.Parse(MapBJ["CivilPrice_HXN"].ToString()) +
                double.Parse(MapBJ["InstallFee_WX"].ToString()) + double.Parse(MapBJ["InstallFee_KBS"].ToString()) +
                double.Parse(MapBJ["InstallFee_GP"].ToString()) + double.Parse(MapBJ["InstallFee_ZP"].ToString()) +
                double.Parse(MapBJ["MeterialFee_ZP"].ToString()) + double.Parse(MapBJ["EquipFee_ZP"].ToString()) +
                double.Parse(MapBJ["MeterialFee_GP"].ToString()) + double.Parse(MapBJ["EquipFee_GP"].ToString()) +
                double.Parse(MapBJ["MeterialFee_KBS"].ToString()) + double.Parse(MapBJ["EquipFee_KBS"].ToString()) +
                double.Parse(MapBJ["MeterialFee_WX"].ToString()) + double.Parse(MapBJ["EquipFee_WX"].ToString()) +
                double.Parse(MapBJ["CivilPrice_WX"].ToString());
            BJHJ = BJXJ + double.Parse(MapBJ["DJHTJE"].ToString()) +
                double.Parse(MapBJ["SJF"].ToString()) * double.Parse(MapBJ["DJHTJE"].ToString()) +
                double.Parse(MapBJ["JLF"].ToString()) * double.Parse(MapBJ["DJHTJE"].ToString()) +
                double.Parse(MapBJ["DJGLF"].ToString()) * double.Parse(MapBJ["DJHTJE"].ToString()) +
                double.Parse(MapBJ["SJ"].ToString()) * double.Parse(MapBJ["DJHTJE"].ToString()) +
                double.Parse(MapBJ["ZJCB"].ToString()) +
                double.Parse(MapBJ["GSGLF"].ToString()) * double.Parse(MapBJ["DJHTJE"].ToString()) +
                double.Parse(MapBJ["CoordFee"].ToString());

            CSXJ = double.Parse(MapCS["YZYQ_CDZ"].ToString()) + double.Parse(MapCS["YZYQ_LLLJ"].ToString()) +
                double.Parse(MapCS["YZYQ_YHYB"].ToString()) + double.Parse(MapCS["YZYQ_CF"].ToString()) +
                double.Parse(MapCS["TestFee"].ToString()) + double.Parse(MapCS["CivilPrice_HXN"].ToString()) +
                double.Parse(MapCS["InstallFee_WX"].ToString()) + double.Parse(MapCS["InstallFee_KBS"].ToString()) +
                double.Parse(MapCS["InstallFee_GP"].ToString()) + double.Parse(MapCS["InstallFee_ZP"].ToString()) +
                double.Parse(MapCS["MeterialFee_ZP"].ToString()) + double.Parse(MapCS["EquipFee_ZP"].ToString()) +
                double.Parse(MapCS["MeterialFee_GP"].ToString()) + double.Parse(MapCS["EquipFee_GP"].ToString()) +
                double.Parse(MapCS["MeterialFee_KBS"].ToString()) + double.Parse(MapCS["EquipFee_KBS"].ToString()) +
                double.Parse(MapCS["MeterialFee_WX"].ToString()) + double.Parse(MapCS["EquipFee_WX"].ToString()) +
                double.Parse(MapCS["CivilPrice_WX"].ToString());
            CSHJ = CSXJ + double.Parse(MapCS["DJHTJE"].ToString()) +
                double.Parse(MapCS["SJF"].ToString()) * double.Parse(MapCS["DJHTJE"].ToString()) +
                double.Parse(MapCS["JLF"].ToString()) * double.Parse(MapCS["DJHTJE"].ToString()) +
                double.Parse(MapCS["DJGLF"].ToString()) * double.Parse(MapCS["DJHTJE"].ToString()) +
                double.Parse(MapCS["SJ"].ToString()) * double.Parse(MapCS["DJHTJE"].ToString()) +
                double.Parse(MapCS["ZJCB"].ToString()) +
                double.Parse(MapCS["GSGLF"].ToString()) * double.Parse(MapCS["DJHTJE"].ToString()) +
                double.Parse(MapCS["CoordFee"].ToString());

            //第0行
            IRow row0 = sheet1.CreateRow(currRow);   //创建行
            IRow rowSum;
            currRow++;
            ICell cell = row0.CreateCell(0);                     //创建列

            temp = ProjectName + "——" + Name + "(前期测算表)";
            cell.SetCellValue(temp);
            setCellStyle(book, cell);
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 7));
            //第1行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("项目名称");
            row0.CreateCell(1).SetCellValue(MapCS["XMMC"].ToString());
            row0.CreateCell(2).SetCellValue("用电性质");
            row0.CreateCell(3).SetCellValue(MapCS["YDXZ"].ToString());
            row0.CreateCell(4).SetCellValue("所属区域");
            row0.CreateCell(5).SetCellValue(MapCS["SSQY"].ToString());
            row0.CreateCell(6).SetCellValue("合同金额");
            row0.CreateCell(7).SetCellValue(MapCS["HTJE"].ToString());
            //第2行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("建筑面积");
            row0.CreateCell(1).SetCellValue(MapCS["JZMJ"].ToString());
            row0.CreateCell(2).SetCellValue("容量(KVA)");
            row0.CreateCell(3).SetCellValue(MapCS["RL"].ToString());
            row0.CreateCell(4).SetCellValue("外线长度");
            row0.CreateCell(5).SetCellValue(MapCS["WXCD"].ToString());
            row0.CreateCell(6).SetCellValue("红线内报价");
            row0.CreateCell(7).SetCellValue(MapCS["HXNBJJE"].ToString());
            //第3行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("红线内单方造价");
            row0.CreateCell(1).SetCellValue((double.Parse(MapCS["HXNBJJE"].ToString()) / double.Parse(MapCS["JZMJ"].ToString())).ToString("F2"));
            row0.CreateCell(2).SetCellValue("红线内容量单价");
            row0.CreateCell(3).SetCellValue((double.Parse(MapCS["HXNBJJE"].ToString()) / double.Parse(MapCS["RL"].ToString())).ToString("F2"));
            row0.CreateCell(4).SetCellValue("单方造价");
            row0.CreateCell(5).SetCellValue((double.Parse(MapCS["HTJE"].ToString()) / double.Parse(MapCS["JZMJ"].ToString())).ToString("F2"));
            row0.CreateCell(6).SetCellValue("容量单价");
            row0.CreateCell(7).SetCellValue((double.Parse(MapCS["HTJE"].ToString()) / double.Parse(MapCS["RL"].ToString())).ToString("F2"));
            //第4行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("费用类型");
            row0.CreateCell(1).SetCellValue("费用类型");
            row0.CreateCell(2).SetCellValue("费用目录");
            row0.CreateCell(3).SetCellValue("报价成本测算");
            row0.CreateCell(4).SetCellValue("实际成本测算");
            row0.CreateCell(5).SetCellValue("总成本占比");
            row0.CreateCell(6).SetCellValue("合同金额占比");
            row0.CreateCell(7).SetCellValue("备注");
            //第5行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程直接成本");
            row0.CreateCell(1).SetCellValue("外线");
            row0.CreateCell(2).SetCellValue("外线土建施工费");
            row0.CreateCell(3).SetCellValue(MapBJ["CivilPrice_WX"].ToString());
            row0.CreateCell(4).SetCellValue(MapCS["CivilPrice_WX"].ToString());
            row0.CreateCell(5).SetCellValue((double.Parse(MapCS["CivilPrice_WX"].ToString()) / CSHJ).ToString("F3"));
            row0.CreateCell(6).SetCellValue((double.Parse(MapCS["CivilPrice_WX"].ToString()) / HTJE).ToString("F3"));
            row0.CreateCell(7).SetCellValue(MapCS["Memo_WXTJ"].ToString());
            //第6行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程直接成本");
            row0.CreateCell(1).SetCellValue("外线");
            row0.CreateCell(2).SetCellValue("外线设备费");
            row0.CreateCell(3).SetCellValue(MapBJ["EquipFee_WX"].ToString());
            row0.CreateCell(4).SetCellValue(MapCS["EquipFee_WX"].ToString());
            row0.CreateCell(5).SetCellValue((double.Parse(MapCS["EquipFee_WX"].ToString()) / CSHJ).ToString("F3"));
            row0.CreateCell(6).SetCellValue((double.Parse(MapCS["EquipFee_WX"].ToString()) / HTJE).ToString("F3"));
            row0.CreateCell(7).SetCellValue(MapCS["Memo_WXSB"].ToString());
            //第7行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程直接成本");
            row0.CreateCell(1).SetCellValue("外线");
            row0.CreateCell(2).SetCellValue("外线材料费");
            row0.CreateCell(3).SetCellValue(MapBJ["MeterialFee_WX"].ToString());
            row0.CreateCell(4).SetCellValue(MapCS["MeterialFee_WX"].ToString());
            row0.CreateCell(5).SetCellValue((double.Parse(MapCS["MeterialFee_WX"].ToString()) / CSHJ).ToString("F3"));
            row0.CreateCell(6).SetCellValue((double.Parse(MapCS["MeterialFee_WX"].ToString()) / HTJE).ToString("F3"));
            row0.CreateCell(7).SetCellValue(MapCS["Memo_WXCL"].ToString());
            //第8行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程直接成本");
            row0.CreateCell(1).SetCellValue("开闭所");
            row0.CreateCell(2).SetCellValue("设备费");
            row0.CreateCell(3).SetCellValue(MapBJ["EquipFee_KBS"].ToString());
            row0.CreateCell(4).SetCellValue(MapCS["EquipFee_KBS"].ToString());
            row0.CreateCell(5).SetCellValue((double.Parse(MapCS["EquipFee_KBS"].ToString()) / CSHJ).ToString("F3"));
            row0.CreateCell(6).SetCellValue((double.Parse(MapCS["EquipFee_KBS"].ToString()) / HTJE).ToString("F3"));
            row0.CreateCell(7).SetCellValue(MapCS["Memo_KBSSB"].ToString());
            //第9行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程直接成本");
            row0.CreateCell(1).SetCellValue("开闭所");
            row0.CreateCell(2).SetCellValue("材料费");
            row0.CreateCell(3).SetCellValue(MapBJ["MeterialFee_KBS"].ToString());
            row0.CreateCell(4).SetCellValue(MapCS["MeterialFee_KBS"].ToString());
            row0.CreateCell(5).SetCellValue((double.Parse(MapCS["MeterialFee_KBS"].ToString()) / CSHJ).ToString("F3"));
            row0.CreateCell(6).SetCellValue((double.Parse(MapCS["MeterialFee_KBS"].ToString()) / HTJE).ToString("F3"));
            row0.CreateCell(7).SetCellValue(MapCS["Memo_KBSCL"].ToString());
            //第10行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程直接成本");
            row0.CreateCell(1).SetCellValue("公用");
            row0.CreateCell(2).SetCellValue("设备费");
            row0.CreateCell(3).SetCellValue(MapBJ["EquipFee_GP"].ToString());
            row0.CreateCell(4).SetCellValue(MapCS["EquipFee_GP"].ToString());
            row0.CreateCell(5).SetCellValue((double.Parse(MapCS["EquipFee_GP"].ToString()) / CSHJ).ToString("F3"));
            row0.CreateCell(6).SetCellValue((double.Parse(MapCS["EquipFee_GP"].ToString()) / HTJE).ToString("F3"));
            row0.CreateCell(7).SetCellValue(MapCS["Memo_GYSB"].ToString());
            //第11行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程直接成本");
            row0.CreateCell(1).SetCellValue("公用");
            row0.CreateCell(2).SetCellValue("材料费");
            row0.CreateCell(3).SetCellValue(MapBJ["MeterialFee_GP"].ToString());
            row0.CreateCell(4).SetCellValue(MapCS["MeterialFee_GP"].ToString());
            row0.CreateCell(5).SetCellValue((double.Parse(MapCS["MeterialFee_GP"].ToString()) / CSHJ).ToString("F3"));
            row0.CreateCell(6).SetCellValue((double.Parse(MapCS["MeterialFee_GP"].ToString()) / HTJE).ToString("F3"));
            row0.CreateCell(7).SetCellValue(MapCS["Memo_GYCL"].ToString());
            //第12行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程直接成本");
            row0.CreateCell(1).SetCellValue("专用");
            row0.CreateCell(2).SetCellValue("设备费");
            row0.CreateCell(3).SetCellValue(MapBJ["EquipFee_ZP"].ToString());
            row0.CreateCell(4).SetCellValue(MapCS["EquipFee_ZP"].ToString());
            row0.CreateCell(5).SetCellValue((double.Parse(MapCS["EquipFee_ZP"].ToString()) / CSHJ).ToString("F3"));
            row0.CreateCell(6).SetCellValue((double.Parse(MapCS["EquipFee_ZP"].ToString()) / HTJE).ToString("F3"));
            row0.CreateCell(7).SetCellValue(MapCS["Memo_ZYSB"].ToString());
            //第13行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程直接成本");
            row0.CreateCell(1).SetCellValue("专用");
            row0.CreateCell(2).SetCellValue("材料费");
            row0.CreateCell(3).SetCellValue(MapBJ["MeterialFee_ZP"].ToString());
            row0.CreateCell(4).SetCellValue(MapCS["MeterialFee_ZP"].ToString());
            row0.CreateCell(5).SetCellValue((double.Parse(MapCS["MeterialFee_ZP"].ToString()) / CSHJ).ToString("F3"));
            row0.CreateCell(6).SetCellValue((double.Parse(MapCS["MeterialFee_ZP"].ToString()) / HTJE).ToString("F3"));
            row0.CreateCell(7).SetCellValue(MapCS["Memo_ZYCL"].ToString());
            //第14行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程直接成本");
            row0.CreateCell(1).SetCellValue("总体");
            row0.CreateCell(2).SetCellValue("安装施工费");
            tempNum1 = double.Parse(MapBJ["InstallFee_WX"].ToString()) + double.Parse(MapBJ["InstallFee_KBS"].ToString()) +
                double.Parse(MapBJ["InstallFee_GP"].ToString()) + double.Parse(MapBJ["InstallFee_ZP"].ToString());
            tempNum2= double.Parse(MapCS["InstallFee_WX"].ToString()) + double.Parse(MapCS["InstallFee_KBS"].ToString()) +
                double.Parse(MapCS["InstallFee_GP"].ToString()) + double.Parse(MapCS["InstallFee_ZP"].ToString());
            row0.CreateCell(3).SetCellValue(tempNum1.ToString("F2"));
            row0.CreateCell(4).SetCellValue(tempNum2.ToString("F2"));
            row0.CreateCell(5).SetCellValue((tempNum2 / CSHJ).ToString("F3"));
            row0.CreateCell(6).SetCellValue((tempNum2 / HTJE).ToString("F3"));
            row0.CreateCell(7).SetCellValue(MapCS["Memo_ZTAZ"].ToString());
            //第15行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程直接成本");
            row0.CreateCell(1).SetCellValue("总体");
            row0.CreateCell(2).SetCellValue("土建施工费（红线内）");
            row0.CreateCell(3).SetCellValue(MapBJ["CivilPrice_HXN"].ToString());
            row0.CreateCell(4).SetCellValue(MapCS["CivilPrice_HXN"].ToString());
            row0.CreateCell(5).SetCellValue((double.Parse(MapCS["CivilPrice_HXN"].ToString()) / CSHJ).ToString("F3"));
            row0.CreateCell(6).SetCellValue((double.Parse(MapCS["CivilPrice_HXN"].ToString()) / HTJE).ToString("F3"));
            row0.CreateCell(7).SetCellValue(MapCS["Memo_ZTTJSGF"].ToString());
            //第16行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程直接成本");
            row0.CreateCell(1).SetCellValue("总体");
            row0.CreateCell(2).SetCellValue("实验调试费");
            row0.CreateCell(3).SetCellValue(MapBJ["TestFee"].ToString());
            row0.CreateCell(4).SetCellValue(MapCS["TestFee"].ToString());
            row0.CreateCell(5).SetCellValue((double.Parse(MapCS["TestFee"].ToString()) / CSHJ).ToString("F3"));
            row0.CreateCell(6).SetCellValue((double.Parse(MapCS["TestFee"].ToString()) / HTJE).ToString("F3"));
            row0.CreateCell(7).SetCellValue(MapCS["Memo_ZTSYTSF"].ToString());
            //第17行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程直接成本");
            row0.CreateCell(1).SetCellValue("业主要求");
            row0.CreateCell(2).SetCellValue("柴发");
            row0.CreateCell(3).SetCellValue(MapBJ["YZYQ_CF"].ToString());
            row0.CreateCell(4).SetCellValue(MapCS["YZYQ_CF"].ToString());
            row0.CreateCell(5).SetCellValue((double.Parse(MapCS["YZYQ_CF"].ToString()) / CSHJ).ToString("F3"));
            row0.CreateCell(6).SetCellValue((double.Parse(MapCS["YZYQ_CF"].ToString()) / HTJE).ToString("F3"));
            row0.CreateCell(7).SetCellValue(MapCS["Memo_YZYQCF"].ToString());
            //第18行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程直接成本");
            row0.CreateCell(1).SetCellValue("业主要求");
            row0.CreateCell(2).SetCellValue("一户一表");
            row0.CreateCell(3).SetCellValue(MapBJ["YZYQ_YHYB"].ToString());
            row0.CreateCell(4).SetCellValue(MapCS["YZYQ_YHYB"].ToString());
            row0.CreateCell(5).SetCellValue((double.Parse(MapCS["YZYQ_YHYB"].ToString()) / CSHJ).ToString("F3"));
            row0.CreateCell(6).SetCellValue((double.Parse(MapCS["YZYQ_YHYB"].ToString()) / HTJE).ToString("F3"));
            row0.CreateCell(7).SetCellValue(MapCS["Memo_YZYQYHYB"].ToString());
            //第19行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程直接成本");
            row0.CreateCell(1).SetCellValue("业主要求");
            row0.CreateCell(2).SetCellValue("联络路径");
            row0.CreateCell(3).SetCellValue(MapBJ["YZYQ_LLLJ"].ToString());
            row0.CreateCell(4).SetCellValue(MapCS["YZYQ_LLLJ"].ToString());
            row0.CreateCell(5).SetCellValue((double.Parse(MapCS["YZYQ_LLLJ"].ToString()) / CSHJ).ToString("F3"));
            row0.CreateCell(6).SetCellValue((double.Parse(MapCS["YZYQ_LLLJ"].ToString()) / HTJE).ToString("F3"));
            row0.CreateCell(7).SetCellValue(MapCS["Memo_YZYQLLLJ"].ToString());
            //第20行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程直接成本");
            row0.CreateCell(1).SetCellValue("业主要求");
            row0.CreateCell(2).SetCellValue("充电桩");
            row0.CreateCell(3).SetCellValue(MapBJ["YZYQ_CDZ"].ToString());
            row0.CreateCell(4).SetCellValue(MapCS["YZYQ_CDZ"].ToString());
            row0.CreateCell(5).SetCellValue((double.Parse(MapCS["YZYQ_CDZ"].ToString()) / CSHJ).ToString("F3"));
            row0.CreateCell(6).SetCellValue((double.Parse(MapCS["YZYQ_CDZ"].ToString()) / HTJE).ToString("F3"));
            row0.CreateCell(7).SetCellValue(MapCS["Memo_YZYQCDZ"].ToString());
            //第21行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("小计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue(BJXJ.ToString("F2"));
            row0.CreateCell(4).SetCellValue(CSXJ.ToString("F2"));
            row0.CreateCell(5).SetCellValue((CSXJ / CSHJ).ToString("F3"));
            row0.CreateCell(6).SetCellValue((CSXJ / HTJE).ToString("F3"));
            row0.CreateCell(7).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 2));
            //第22行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程管理成本");
            row0.CreateCell(1).SetCellValue("多径合同金额(元)");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue(MapBJ["DJHTJE"].ToString());
            row0.CreateCell(4).SetCellValue(MapCS["DJHTJE"].ToString());
            row0.CreateCell(5).SetCellValue((double.Parse(MapCS["DJHTJE"].ToString()) / CSHJ).ToString("F3"));
            row0.CreateCell(6).SetCellValue((double.Parse(MapCS["DJHTJE"].ToString()) / HTJE).ToString("F3"));
            row0.CreateCell(7).SetCellValue(MapCS["Memo_DJHTJE"].ToString());
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 1, 2));
            //第23行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程管理成本");
            row0.CreateCell(1).SetCellValue("电力局费用");
            row0.CreateCell(2).SetCellValue("设计费");
            row0.CreateCell(3).SetCellValue((double.Parse(MapBJ["SJF"].ToString())* double.Parse(MapBJ["DJHTJE"].ToString())).ToString("F2"));
            row0.CreateCell(4).SetCellValue((double.Parse(MapCS["SJF"].ToString()) * double.Parse(MapCS["DJHTJE"].ToString())).ToString("F2"));
            row0.CreateCell(5).SetCellValue((double.Parse(MapCS["SJF"].ToString()) * double.Parse(MapCS["DJHTJE"].ToString())/CSHJ).ToString("F2"));
            row0.CreateCell(6).SetCellValue((double.Parse(MapCS["SJF"].ToString()) * double.Parse(MapCS["DJHTJE"].ToString()) / HTJE).ToString("F2"));
            row0.CreateCell(7).SetCellValue(MapCS["SJF"].ToString());
            row0.CreateCell(8).SetCellValue(MapCS["Memo_SJF"].ToString());
            //第24行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程管理成本");
            row0.CreateCell(1).SetCellValue("电力局费用");
            row0.CreateCell(2).SetCellValue("监理费");
            row0.CreateCell(3).SetCellValue((double.Parse(MapBJ["JLF"].ToString()) * double.Parse(MapBJ["DJHTJE"].ToString())).ToString("F2"));
            row0.CreateCell(4).SetCellValue((double.Parse(MapCS["JLF"].ToString()) * double.Parse(MapCS["DJHTJE"].ToString())).ToString("F2"));
            row0.CreateCell(5).SetCellValue((double.Parse(MapCS["JLF"].ToString()) * double.Parse(MapCS["DJHTJE"].ToString()) / CSHJ).ToString("F2"));
            row0.CreateCell(6).SetCellValue((double.Parse(MapCS["JLF"].ToString()) * double.Parse(MapCS["DJHTJE"].ToString()) / HTJE).ToString("F2"));
            row0.CreateCell(7).SetCellValue(MapCS["JLF"].ToString());
            row0.CreateCell(8).SetCellValue(MapCS["Memo_JLF"].ToString());
            //第25行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程管理成本");
            row0.CreateCell(1).SetCellValue("电力局费用");
            row0.CreateCell(2).SetCellValue("多径管理费");
            row0.CreateCell(3).SetCellValue((double.Parse(MapBJ["DJGLF"].ToString()) * double.Parse(MapBJ["DJHTJE"].ToString())).ToString("F2"));
            row0.CreateCell(4).SetCellValue((double.Parse(MapCS["DJGLF"].ToString()) * double.Parse(MapCS["DJHTJE"].ToString())).ToString("F2"));
            row0.CreateCell(5).SetCellValue((double.Parse(MapCS["DJGLF"].ToString()) * double.Parse(MapCS["DJHTJE"].ToString()) / CSHJ).ToString("F2"));
            row0.CreateCell(6).SetCellValue((double.Parse(MapCS["DJGLF"].ToString()) * double.Parse(MapCS["DJHTJE"].ToString()) / HTJE).ToString("F2"));
            row0.CreateCell(7).SetCellValue(MapCS["DJGLF"].ToString());
            row0.CreateCell(8).SetCellValue(MapCS["Memo_DJGLF"].ToString());
            //第26行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程管理成本");
            row0.CreateCell(1).SetCellValue("公司费用");
            row0.CreateCell(2).SetCellValue("税金");
            row0.CreateCell(3).SetCellValue((double.Parse(MapBJ["SJ"].ToString()) * double.Parse(MapBJ["DJHTJE"].ToString())).ToString("F2"));
            row0.CreateCell(4).SetCellValue((double.Parse(MapCS["SJ"].ToString()) * double.Parse(MapCS["DJHTJE"].ToString())).ToString("F2"));
            row0.CreateCell(5).SetCellValue((double.Parse(MapCS["SJ"].ToString()) * double.Parse(MapCS["DJHTJE"].ToString()) / CSHJ).ToString("F2"));
            row0.CreateCell(6).SetCellValue((double.Parse(MapCS["SJ"].ToString()) * double.Parse(MapCS["DJHTJE"].ToString()) / HTJE).ToString("F2"));
            row0.CreateCell(7).SetCellValue(MapCS["SJ"].ToString());
            row0.CreateCell(8).SetCellValue(MapCS["Memo_SJ"].ToString());
            //第27行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程管理成本");
            row0.CreateCell(1).SetCellValue("公司费用");
            row0.CreateCell(2).SetCellValue("资金成本");
            row0.CreateCell(3).SetCellValue(double.Parse(MapBJ["ZJCB"].ToString()).ToString("F2"));
            row0.CreateCell(4).SetCellValue(double.Parse(MapCS["ZJCB"].ToString()).ToString("F2"));
            row0.CreateCell(5).SetCellValue((double.Parse(MapCS["ZJCB"].ToString()) / CSHJ).ToString("F2"));
            row0.CreateCell(6).SetCellValue((double.Parse(MapCS["ZJCB"].ToString()) / HTJE).ToString("F2"));
            row0.CreateCell(7).SetCellValue(MapCS["ZJCB"].ToString());
            row0.CreateCell(8).SetCellValue(MapCS["Memo_ZJCB"].ToString());
            //第28行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程管理成本");
            row0.CreateCell(1).SetCellValue("公司费用");
            row0.CreateCell(2).SetCellValue("公司管理费");
            row0.CreateCell(3).SetCellValue((double.Parse(MapBJ["GSGLF"].ToString()) * double.Parse(MapBJ["DJHTJE"].ToString())).ToString("F2"));
            row0.CreateCell(4).SetCellValue((double.Parse(MapCS["GSGLF"].ToString()) * double.Parse(MapCS["DJHTJE"].ToString())).ToString("F2"));
            row0.CreateCell(5).SetCellValue((double.Parse(MapCS["GSGLF"].ToString()) * double.Parse(MapCS["DJHTJE"].ToString()) / CSHJ).ToString("F2"));
            row0.CreateCell(6).SetCellValue((double.Parse(MapCS["GSGLF"].ToString()) * double.Parse(MapCS["DJHTJE"].ToString()) / HTJE).ToString("F2"));
            row0.CreateCell(7).SetCellValue(MapCS["GSGLF"].ToString());
            row0.CreateCell(8).SetCellValue(MapCS["Memo_GSGLF"].ToString());
            //第29行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程管理成本");
            row0.CreateCell(1).SetCellValue("公司费用");
            row0.CreateCell(2).SetCellValue("业务费");
            row0.CreateCell(3).SetCellValue(double.Parse(MapBJ["CoordFee"].ToString()).ToString("F2"));
            row0.CreateCell(4).SetCellValue(double.Parse(MapCS["CoordFee"].ToString()).ToString("F2"));
            row0.CreateCell(5).SetCellValue((double.Parse(MapCS["CoordFee"].ToString()) / CSHJ).ToString("F2"));
            row0.CreateCell(6).SetCellValue((double.Parse(MapCS["CoordFee"].ToString()) / HTJE).ToString("F2"));
            row0.CreateCell(7).SetCellValue(MapCS["Memo_ZJCB"].ToString());
            //第30行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("成本合计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue(BJHJ.ToString("F2"));
            row0.CreateCell(4).SetCellValue(CSHJ.ToString("F2"));
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 2));
            //第31行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("利润");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue((BJHJ * double.Parse(MapBJ["LRL"].ToString()) ) .ToString("F2"));
            row0.CreateCell(4).SetCellValue((HTJE-CSHJ).ToString());
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 2));

            //最后合并单元格
            sheet1.AddMergedRegion(new CellRangeAddress(5, 20, 0, 0));
            sheet1.AddMergedRegion(new CellRangeAddress(5, 7, 1, 1));
            sheet1.AddMergedRegion(new CellRangeAddress(8, 9, 1, 1));
            sheet1.AddMergedRegion(new CellRangeAddress(10, 11, 1, 1));
            sheet1.AddMergedRegion(new CellRangeAddress(12, 13, 1, 1));
            sheet1.AddMergedRegion(new CellRangeAddress(14, 16, 1, 1));
            sheet1.AddMergedRegion(new CellRangeAddress(17, 20, 1, 1));

            sheet1.AddMergedRegion(new CellRangeAddress(22, 29, 0, 0));
            sheet1.AddMergedRegion(new CellRangeAddress(23, 25, 1, 1));
            sheet1.AddMergedRegion(new CellRangeAddress(26, 29, 1, 1));

            //刷新单元格格式
            ICell commCell;
            for (int i = 1; i < 32; i++)
            {
                for (int j = 0; j < 8; j++)
                {
                    commCell = sheet1.GetRow(i).GetCell(j);

                    //CommCellStyle(book, commCell);
                    commCell.CellStyle = fCellStyle;
                }
            }
            sheet2 = EstUserInstallSheet(PreEstId, sheet2, fCellStyle);
            sheet3 = EstUserCivilSheet(PreEstId, sheet3, fCellStyle);
            sheet4 = EstUserEquipSheet(PreEstId, sheet4, fCellStyle);
            sheet5 = EstUserMaterialSheet(PreEstId, sheet5, fCellStyle);
            sheet6 = EstUserTestSheet(PreEstId, sheet6, fCellStyle);
            sheet7 = EstUserCoordFeeSheet(PreEstId, sheet7, fCellStyle);
            sheet8 = EstUserCivilSheetDetail(PreEstId, sheet8, fCellStyle, book);


            return book;

        }


        public ISheet EstUserInstallSheet(string PreEstId, ISheet Sheet1, XSSFCellStyle fCellStyle)
        {
            string sql = "";
            //WX
            sql = "SELECT Name,Specification,Content,Unit,Quantity,QuoteMoney,Quantity*QuoteMoney as Sum,Memo,TabType " +
               "FROM o_PEInstallFee where TabType='WX' and IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程前期测算' ORDER BY Specification";
            var ListWX = dao.GetList(sql, new string[] { "Name", "Specification", "Content", "Unit", "Quantity", "QuoteMoney", "Sum", "Memo", "TabType" });
            //清零操作
            for (int i = 0; i < ListWX.Count; i++)
            {
                if (ListWX[i]["Sum"].ToString() == "")
                {
                    ListWX[i]["Sum"] = 0;
                }
            }
            //KBS
            sql = "SELECT Name,Specification,Content,Unit,Quantity,QuoteMoney,Quantity*QuoteMoney as Sum,Memo,TabType " +
               "FROM o_PEInstallFee where TabType='KBS' and IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程前期测算' ORDER BY Specification";
            var ListKBS = dao.GetList(sql, new string[] { "Name", "Specification", "Content", "Unit", "Quantity", "QuoteMoney", "Sum", "Memo", "TabType" });
            //清零操作
            for (int i = 0; i < ListKBS.Count; i++)
            {
                if (ListKBS[i]["Sum"].ToString() == "")
                {
                    ListKBS[i]["Sum"] = 0;
                }
            }
            //GP
            sql = "SELECT Name,Specification,Content,Unit,Quantity,QuoteMoney,Quantity*QuoteMoney as Sum,Memo,TabType " +
               "FROM o_PEInstallFee where TabType='GP' and IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程前期测算' ORDER BY Specification";
            var ListGP = dao.GetList(sql, new string[] { "Name", "Specification", "Content", "Unit", "Quantity", "QuoteMoney", "Sum", "Memo", "TabType" });
            //清零操作
            for (int i = 0; i < ListGP.Count; i++)
            {
                if (ListGP[i]["Sum"].ToString() == "")
                {
                    ListGP[i]["Sum"] = 0;
                }
            }
            //ZP
            sql = "SELECT Name,Specification,Content,Unit,Quantity,QuoteMoney,Quantity*QuoteMoney as Sum,Memo,TabType " +
               "FROM o_PEInstallFee where TabType='ZP' and IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程前期测算' ORDER BY Specification";
            var ListZP = dao.GetList(sql, new string[] { "Name", "Specification", "Content", "Unit", "Quantity", "QuoteMoney", "Sum", "Memo", "TabType" });
            //清零操作
            for (int i = 0; i < ListZP.Count; i++)
            {
                if (ListZP[i]["Sum"].ToString() == "")
                {
                    ListZP[i]["Sum"] = 0;
                }
            }
            XSSFWorkbook book = new XSSFWorkbook();
            XSSFCellStyle fCellStyle2 = (XSSFCellStyle)book.CreateCellStyle();
            XSSFFont ffont = (XSSFFont)book.CreateFont();
            ffont.FontHeight = 15 * 20;
            ffont.FontName = "宋体";
            ffont.Color = new XSSFColor(Color.Black).Indexed;
            fCellStyle2.SetFont(ffont);
            fCellStyle2.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直对齐
            fCellStyle2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平对齐


            Sheet1.SetColumnWidth(0, 5000);
            Sheet1.SetColumnWidth(1, 8000);
            Sheet1.SetColumnWidth(2, 5000);
            Sheet1.SetColumnWidth(3, 2000);
            Sheet1.SetColumnWidth(4, 2000);
            Sheet1.SetColumnWidth(5, 3000);
            Sheet1.SetColumnWidth(6, 4000);
            Sheet1.SetColumnWidth(7, 5000);

            double Sum = 0;
            double tempSum = 0;
            int currRow = 0;
            bool tagTitle = true;//表面建立表头
            IRow rowSum;
            string Type = "";
            //设置表头
            IRow row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("项目名称");
            row0.CreateCell(1).SetCellValue("规格型号");
            row0.CreateCell(2).SetCellValue("主要工作内容");
            row0.CreateCell(3).SetCellValue("单位");
            row0.CreateCell(4).SetCellValue("工程量");
            row0.CreateCell(5).SetCellValue("单价");
            row0.CreateCell(6).SetCellValue("合价");
            row0.CreateCell(7).SetCellValue("备注");

            ////////////////////////////////////////////////////
            //外线循环
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("外线部分");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 7));

            for (int i = 0; i < ListWX.Count; i++)
            {

                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListWX[i]["Name"].ToString());
                row0.CreateCell(1).SetCellValue(ListWX[i]["Specification"].ToString());
                row0.CreateCell(2).SetCellValue(ListWX[i]["Content"].ToString());
                row0.CreateCell(3).SetCellValue(ListWX[i]["Unit"].ToString());
                row0.CreateCell(4).SetCellValue(ListWX[i]["Quantity"].ToString());
                row0.CreateCell(5).SetCellValue(ListWX[i]["QuoteMoney"].ToString());
                row0.CreateCell(6).SetCellValue(ListWX[i]["Sum"].ToString());
                row0.CreateCell(7).SetCellValue(ListWX[i]["Memo"].ToString());
                //Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 7));

                tempSum += double.Parse(ListWX[i]["Sum"].ToString());
            }
            //小计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("小计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue(tempSum.ToString("F2"));
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
            Sum += tempSum;

            ////////////////////////////////////////////////////
            //开闭所循环
            tempSum = 0;
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("开闭所部分");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 7));

            for (int i = 0; i < ListKBS.Count; i++)
            {

                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListKBS[i]["Name"].ToString());
                row0.CreateCell(1).SetCellValue(ListKBS[i]["Specification"].ToString());
                row0.CreateCell(2).SetCellValue(ListKBS[i]["Content"].ToString());
                row0.CreateCell(3).SetCellValue(ListKBS[i]["Unit"].ToString());
                row0.CreateCell(4).SetCellValue(ListKBS[i]["Quantity"].ToString());
                row0.CreateCell(5).SetCellValue(ListKBS[i]["QuoteMoney"].ToString());
                row0.CreateCell(6).SetCellValue(ListKBS[i]["Sum"].ToString());
                row0.CreateCell(7).SetCellValue(ListKBS[i]["Memo"].ToString());
                //Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 7));

                tempSum += double.Parse(ListKBS[i]["Sum"].ToString());
            }
            //小计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("小计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue(tempSum.ToString("F2"));
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
            Sum += tempSum;

            ////////////////////////////////////////////////////
            //公配循环
            tempSum = 0;
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("公配部分");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 7));

            for (int i = 0; i < ListGP.Count; i++)
            {

                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListGP[i]["Name"].ToString());
                row0.CreateCell(1).SetCellValue(ListGP[i]["Specification"].ToString());
                row0.CreateCell(2).SetCellValue(ListGP[i]["Content"].ToString());
                row0.CreateCell(3).SetCellValue(ListGP[i]["Unit"].ToString());
                row0.CreateCell(4).SetCellValue(ListGP[i]["Quantity"].ToString());
                row0.CreateCell(5).SetCellValue(ListGP[i]["QuoteMoney"].ToString());
                row0.CreateCell(6).SetCellValue(ListGP[i]["Sum"].ToString());
                row0.CreateCell(7).SetCellValue(ListGP[i]["Memo"].ToString());
                //Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 7));

                tempSum += double.Parse(ListGP[i]["Sum"].ToString());
            }
            //小计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("小计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue(tempSum.ToString("F2"));
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
            Sum += tempSum;

            ////////////////////////////////////////////////////
            //专配循环
            tempSum = 0;
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("专配部分");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 7));

            for (int i = 0; i < ListZP.Count; i++)
            {

                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListZP[i]["Name"].ToString());
                row0.CreateCell(1).SetCellValue(ListZP[i]["Specification"].ToString());
                row0.CreateCell(2).SetCellValue(ListZP[i]["Content"].ToString());
                row0.CreateCell(3).SetCellValue(ListZP[i]["Unit"].ToString());
                row0.CreateCell(4).SetCellValue(ListZP[i]["Quantity"].ToString());
                row0.CreateCell(5).SetCellValue(ListZP[i]["QuoteMoney"].ToString());
                row0.CreateCell(6).SetCellValue(ListZP[i]["Sum"].ToString());
                row0.CreateCell(7).SetCellValue(ListZP[i]["Memo"].ToString());
                //Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 7));

                tempSum += double.Parse(ListZP[i]["Sum"].ToString());
            }
            //小计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("小计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue(tempSum.ToString("F2"));
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
            Sum += tempSum;

            //总计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("总计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue(Sum.ToString("F2"));
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));

            //刷新各行格式
            ICell commCell;
            for (int i = 1; i < currRow - 1; i++)
            {
                //Sheet1.GetRow(i).Height = 60 * 20;
                for (int j = 0; j < 8; j++)
                {

                    commCell = Sheet1.GetRow(i).GetCell(j);

                    //CommCellStyle(book, commCell);
                    commCell.CellStyle = fCellStyle;

                }
            }




            return Sheet1;
        }

        public ISheet EstUserEquipSheet(string PreEstId, ISheet Sheet1, XSSFCellStyle fCellStyle)
        {
            string sql = "";
            //WX
            sql = "SELECT Code,EquipName,Specification,Unit,Quantity,QuoteMoney,Quantity*QuoteMoney as Sum " +
               "FROM o_PEEquipFee where TabType='WX' and IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程前期测算' ORDER BY EquipName";
            var ListWX = dao.GetList(sql, new string[] { "Code", "EquipName", "Specification", "Unit", "Quantity", "QuoteMoney", "Sum" });
            //清零操作
            for (int i = 0; i < ListWX.Count; i++)
            {
                if (ListWX[i]["Sum"].ToString() == "")
                {
                    ListWX[i]["Sum"] = 0;
                }
            }
            //KBS
            sql = "SELECT Code,EquipName,Specification,Unit,Quantity,QuoteMoney,Quantity*QuoteMoney as Sum " +
               "FROM o_PEEquipFee where TabType='KBS' and IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程前期测算' ORDER BY EquipName";
            var ListKBS = dao.GetList(sql, new string[] { "Code", "EquipName", "Specification", "Unit", "Quantity", "QuoteMoney", "Sum" });
            //清零操作
            for (int i = 0; i < ListKBS.Count; i++)
            {
                if (ListKBS[i]["Sum"].ToString() == "")
                {
                    ListKBS[i]["Sum"] = 0;
                }
            }
            //GP
            sql = "SELECT Code,EquipName,Specification,Unit,Quantity,QuoteMoney,Quantity*QuoteMoney as Sum " +
               "FROM o_PEEquipFee where TabType='GP' and IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程前期测算' ORDER BY EquipName";
            var ListGP = dao.GetList(sql, new string[] { "Code", "EquipName", "Specification", "Unit", "Quantity", "QuoteMoney", "Sum" });
            //清零操作
            for (int i = 0; i < ListGP.Count; i++)
            {
                if (ListGP[i]["Sum"].ToString() == "")
                {
                    ListGP[i]["Sum"] = 0;
                }
            }
            //ZP
            sql = "SELECT Code,EquipName,Specification,Unit,Quantity,QuoteMoney,Quantity*QuoteMoney as Sum " +
               "FROM o_PEEquipFee where TabType='ZP' and IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程前期测算' ORDER BY EquipName";
            var ListZP = dao.GetList(sql, new string[] { "Code", "EquipName", "Specification", "Unit", "Quantity", "QuoteMoney", "Sum" });
            //清零操作
            for (int i = 0; i < ListZP.Count; i++)
            {
                if (ListZP[i]["Sum"].ToString() == "")
                {
                    ListZP[i]["Sum"] = 0;
                }
            }

            Sheet1.SetColumnWidth(0, 3000);
            Sheet1.SetColumnWidth(1, 4000);
            Sheet1.SetColumnWidth(2, 5000);
            Sheet1.SetColumnWidth(3, 2000);
            Sheet1.SetColumnWidth(4, 2000);
            Sheet1.SetColumnWidth(5, 3000);
            Sheet1.SetColumnWidth(6, 3000);

            double Sum = 0;
            double tempSum = 0;
            int currRow = 0;
            bool tagTitle = true;//表面建立表头
            IRow rowSum;
            string Type = "";
            //设置表头
            IRow row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("编号");
            row0.CreateCell(1).SetCellValue("名称");
            row0.CreateCell(2).SetCellValue("规格型号");
            row0.CreateCell(3).SetCellValue("单位");
            row0.CreateCell(4).SetCellValue("数量");
            row0.CreateCell(5).SetCellValue("含税单价");
            row0.CreateCell(6).SetCellValue("含税合价");


            ////////////////////////////////////////////////////
            //外线循环
            row0 = Sheet1.CreateRow(currRow);   //创建行   
            row0.Height = 20 * 20;
            currRow++;
            row0.CreateCell(0).SetCellValue("外线部分");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 6));

            for (int i = 0; i < ListWX.Count; i++)
            {

                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListWX[i]["Code"].ToString());
                row0.CreateCell(1).SetCellValue(ListWX[i]["EquipName"].ToString());
                row0.CreateCell(2).SetCellValue(ListWX[i]["Specification"].ToString());
                row0.CreateCell(3).SetCellValue(ListWX[i]["Unit"].ToString());
                row0.CreateCell(4).SetCellValue(ListWX[i]["Quantity"].ToString());
                row0.CreateCell(5).SetCellValue(ListWX[i]["QuoteMoney"].ToString());
                row0.CreateCell(6).SetCellValue(ListWX[i]["Sum"].ToString());
                tempSum += double.Parse(ListWX[i]["Sum"].ToString());
            }
            //小计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("小计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue(tempSum.ToString("F2"));
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
            Sum += tempSum;
            tempSum = 0;
            ////////////////////////////////////////////////////
            //开闭所循环
            row0 = Sheet1.CreateRow(currRow);   //创建行   
            row0.Height = 20 * 20;
            currRow++;
            row0.CreateCell(0).SetCellValue("开闭所部分");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 6));

            for (int i = 0; i < ListKBS.Count; i++)
            {

                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListKBS[i]["Code"].ToString());
                row0.CreateCell(1).SetCellValue(ListKBS[i]["EquipName"].ToString());
                row0.CreateCell(2).SetCellValue(ListKBS[i]["Specification"].ToString());
                row0.CreateCell(3).SetCellValue(ListKBS[i]["Unit"].ToString());
                row0.CreateCell(4).SetCellValue(ListKBS[i]["Quantity"].ToString());
                row0.CreateCell(5).SetCellValue(ListKBS[i]["QuoteMoney"].ToString());
                row0.CreateCell(6).SetCellValue(ListKBS[i]["Sum"].ToString());
                tempSum += double.Parse(ListKBS[i]["Sum"].ToString());
            }
            //小计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("小计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue(tempSum.ToString("F2"));
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
            Sum += tempSum;
            tempSum = 0;
            ////////////////////////////////////////////////////
            //公配循环
            row0 = Sheet1.CreateRow(currRow);   //创建行   
            row0.Height = 20 * 20;
            currRow++;
            row0.CreateCell(0).SetCellValue("公配部分");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 6));

            for (int i = 0; i < ListGP.Count; i++)
            {

                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListGP[i]["Code"].ToString());
                row0.CreateCell(1).SetCellValue(ListGP[i]["EquipName"].ToString());
                row0.CreateCell(2).SetCellValue(ListGP[i]["Specification"].ToString());
                row0.CreateCell(3).SetCellValue(ListGP[i]["Unit"].ToString());
                row0.CreateCell(4).SetCellValue(ListGP[i]["Quantity"].ToString());
                row0.CreateCell(5).SetCellValue(ListGP[i]["QuoteMoney"].ToString());
                row0.CreateCell(6).SetCellValue(ListGP[i]["Sum"].ToString());
                tempSum += double.Parse(ListGP[i]["Sum"].ToString());
            }
            //小计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("小计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue(tempSum.ToString("F2"));
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
            Sum += tempSum;
            tempSum = 0;
            ////////////////////////////////////////////////////
            //专配循环
            row0 = Sheet1.CreateRow(currRow);   //创建行   
            row0.Height = 20 * 20;
            currRow++;
            row0.CreateCell(0).SetCellValue("专配部分");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 6));

            for (int i = 0; i < ListZP.Count; i++)
            {

                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListZP[i]["Code"].ToString());
                row0.CreateCell(1).SetCellValue(ListZP[i]["EquipName"].ToString());
                row0.CreateCell(2).SetCellValue(ListZP[i]["Specification"].ToString());
                row0.CreateCell(3).SetCellValue(ListZP[i]["Unit"].ToString());
                row0.CreateCell(4).SetCellValue(ListZP[i]["Quantity"].ToString());
                row0.CreateCell(5).SetCellValue(ListZP[i]["QuoteMoney"].ToString());
                row0.CreateCell(6).SetCellValue(ListZP[i]["Sum"].ToString());
                tempSum += double.Parse(ListZP[i]["Sum"].ToString());
            }
            //小计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("小计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue(tempSum.ToString("F2"));
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
            Sum += tempSum;
            tempSum = 0;

            //合计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("合计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue(Sum.ToString("F2"));
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
            //刷新各行格式
            ICell commCell;
            for (int i = 0; i < currRow; i++)
            {
                //Sheet1.GetRow(i).Height = 60 * 20;
                for (int j = 0; j < 7; j++)
                {
                    commCell = Sheet1.GetRow(i).GetCell(j);
                    //CommCellStyle(book, commCell);
                    commCell.CellStyle = fCellStyle;

                }
            }


            return Sheet1;
        }

        public ISheet EstUserMaterialSheet(string PreEstId, ISheet Sheet1, XSSFCellStyle fCellStyle)
        {
            string sql = "";
            //WX
            sql = "SELECT Code,EquipName,Specification,Unit,Quantity,QuoteMoney,Quantity*QuoteMoney as Sum " +
               "FROM o_PEMaterialFeeUser where TabType='WX' and IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程前期测算' ORDER BY EquipName";
            var ListWX = dao.GetList(sql, new string[] { "Code", "EquipName", "Specification", "Unit", "Quantity", "QuoteMoney", "Sum" });
            //清零操作
            for (int i = 0; i < ListWX.Count; i++)
            {
                if (ListWX[i]["Sum"].ToString() == "")
                {
                    ListWX[i]["Sum"] = 0;
                }
            }
            //KBS
            sql = "SELECT Code,EquipName,Specification,Unit,Quantity,QuoteMoney,Quantity*QuoteMoney as Sum " +
               "FROM o_PEMaterialFeeUser where TabType='KBS' and IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程前期测算' ORDER BY EquipName";
            var ListKBS = dao.GetList(sql, new string[] { "Code", "EquipName", "Specification", "Unit", "Quantity", "QuoteMoney", "Sum" });
            //清零操作
            for (int i = 0; i < ListKBS.Count; i++)
            {
                if (ListKBS[i]["Sum"].ToString() == "")
                {
                    ListKBS[i]["Sum"] = 0;
                }
            }
            //GP
            sql = "SELECT Code,EquipName,Specification,Unit,Quantity,QuoteMoney,Quantity*QuoteMoney as Sum " +
               "FROM o_PEMaterialFeeUser where TabType='GP' and IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程前期测算' ORDER BY EquipName";
            var ListGP = dao.GetList(sql, new string[] { "Code", "EquipName", "Specification", "Unit", "Quantity", "QuoteMoney", "Sum" });
            //清零操作
            for (int i = 0; i < ListGP.Count; i++)
            {
                if (ListGP[i]["Sum"].ToString() == "")
                {
                    ListGP[i]["Sum"] = 0;
                }
            }
            //ZP
            sql = "SELECT Code,EquipName,Specification,Unit,Quantity,QuoteMoney,Quantity*QuoteMoney as Sum " +
               "FROM o_PEMaterialFeeUser where TabType='ZP' and IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程前期测算' ORDER BY EquipName";
            var ListZP = dao.GetList(sql, new string[] { "Code", "EquipName", "Specification", "Unit", "Quantity", "QuoteMoney", "Sum" });
            //清零操作
            for (int i = 0; i < ListZP.Count; i++)
            {
                if (ListZP[i]["Sum"].ToString() == "")
                {
                    ListZP[i]["Sum"] = 0;
                }
            }
            //YXZB
            sql = "SELECT Code,EquipName,Specification,Unit,Quantity,QuoteMoney,Quantity*QuoteMoney as Sum " +
               "FROM o_PEMaterialFeeUser where TabType='YXZB' and IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程前期测算' ORDER BY EquipName";
            var ListYXZB = dao.GetList(sql, new string[] { "Code", "EquipName", "Specification", "Unit", "Quantity", "QuoteMoney", "Sum" });
            //清零操作
            for (int i = 0; i < ListYXZB.Count; i++)
            {
                if (ListYXZB[i]["Sum"].ToString() == "")
                {
                    ListYXZB[i]["Sum"] = 0;
                }
            }
            Sheet1.SetColumnWidth(0, 3000);
            Sheet1.SetColumnWidth(1, 4000);
            Sheet1.SetColumnWidth(2, 5000);
            Sheet1.SetColumnWidth(3, 2000);
            Sheet1.SetColumnWidth(4, 2000);
            Sheet1.SetColumnWidth(5, 3000);
            Sheet1.SetColumnWidth(6, 3000);

            double Sum = 0;
            double tempSum = 0;
            int currRow = 0;
            bool tagTitle = true;//表面建立表头
            IRow rowSum;
            string Type = "";
            //设置表头
            IRow row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("编号");
            row0.CreateCell(1).SetCellValue("名称");
            row0.CreateCell(2).SetCellValue("规格型号");
            row0.CreateCell(3).SetCellValue("单位");
            row0.CreateCell(4).SetCellValue("数量");
            row0.CreateCell(5).SetCellValue("含税单价");
            row0.CreateCell(6).SetCellValue("含税合价");

            ////////////////////////////////////////////////////
            //外线循环
            row0 = Sheet1.CreateRow(currRow);   //创建行   
            row0.Height = 20 * 20;
            currRow++;
            row0.CreateCell(0).SetCellValue("外线部分");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 6));

            for (int i = 0; i < ListWX.Count; i++)
            {

                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListWX[i]["Code"].ToString());
                row0.CreateCell(1).SetCellValue(ListWX[i]["EquipName"].ToString());
                row0.CreateCell(2).SetCellValue(ListWX[i]["Specification"].ToString());
                row0.CreateCell(3).SetCellValue(ListWX[i]["Unit"].ToString());
                row0.CreateCell(4).SetCellValue(ListWX[i]["Quantity"].ToString());
                row0.CreateCell(5).SetCellValue(ListWX[i]["QuoteMoney"].ToString());
                row0.CreateCell(6).SetCellValue(ListWX[i]["Sum"].ToString());
                tempSum += double.Parse(ListWX[i]["Sum"].ToString());
            }
            //小计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("小计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue(tempSum.ToString("F2"));
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
            Sum += tempSum;
            tempSum = 0;
            ////////////////////////////////////////////////////
            //开闭所循环
            row0 = Sheet1.CreateRow(currRow);   //创建行   
            row0.Height = 20 * 20;
            currRow++;
            row0.CreateCell(0).SetCellValue("开闭所部分");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 6));

            for (int i = 0; i < ListKBS.Count; i++)
            {

                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListKBS[i]["Code"].ToString());
                row0.CreateCell(1).SetCellValue(ListKBS[i]["EquipName"].ToString());
                row0.CreateCell(2).SetCellValue(ListKBS[i]["Specification"].ToString());
                row0.CreateCell(3).SetCellValue(ListKBS[i]["Unit"].ToString());
                row0.CreateCell(4).SetCellValue(ListKBS[i]["Quantity"].ToString());
                row0.CreateCell(5).SetCellValue(ListKBS[i]["QuoteMoney"].ToString());
                row0.CreateCell(6).SetCellValue(ListKBS[i]["Sum"].ToString());
                tempSum += double.Parse(ListKBS[i]["Sum"].ToString());
            }
            //小计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("小计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue(tempSum.ToString("F2"));
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
            Sum += tempSum;
            tempSum = 0;
            ////////////////////////////////////////////////////
            //公配循环
            row0 = Sheet1.CreateRow(currRow);   //创建行   
            row0.Height = 20 * 20;
            currRow++;
            row0.CreateCell(0).SetCellValue("公配部分");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 6));

            for (int i = 0; i < ListGP.Count; i++)
            {

                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListGP[i]["Code"].ToString());
                row0.CreateCell(1).SetCellValue(ListGP[i]["EquipName"].ToString());
                row0.CreateCell(2).SetCellValue(ListGP[i]["Specification"].ToString());
                row0.CreateCell(3).SetCellValue(ListGP[i]["Unit"].ToString());
                row0.CreateCell(4).SetCellValue(ListGP[i]["Quantity"].ToString());
                row0.CreateCell(5).SetCellValue(ListGP[i]["QuoteMoney"].ToString());
                row0.CreateCell(6).SetCellValue(ListGP[i]["Sum"].ToString());
                tempSum += double.Parse(ListGP[i]["Sum"].ToString());
            }
            //小计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("小计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue(tempSum.ToString("F2"));
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
            Sum += tempSum;
            tempSum = 0;
            ////////////////////////////////////////////////////
            //专配循环
            row0 = Sheet1.CreateRow(currRow);   //创建行   
            row0.Height = 20 * 20;
            currRow++;
            row0.CreateCell(0).SetCellValue("专配部分");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 6));

            for (int i = 0; i < ListZP.Count; i++)
            {

                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListZP[i]["Code"].ToString());
                row0.CreateCell(1).SetCellValue(ListZP[i]["EquipName"].ToString());
                row0.CreateCell(2).SetCellValue(ListZP[i]["Specification"].ToString());
                row0.CreateCell(3).SetCellValue(ListZP[i]["Unit"].ToString());
                row0.CreateCell(4).SetCellValue(ListZP[i]["Quantity"].ToString());
                row0.CreateCell(5).SetCellValue(ListZP[i]["QuoteMoney"].ToString());
                row0.CreateCell(6).SetCellValue(ListZP[i]["Sum"].ToString());
                tempSum += double.Parse(ListZP[i]["Sum"].ToString());
            }
            //小计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("小计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue(tempSum.ToString("F2"));
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
            Sum += tempSum;
            tempSum = 0;

            ////////////////////////////////////////////////////
            //运行准备循环
            row0 = Sheet1.CreateRow(currRow);   //创建行   
            row0.Height = 20 * 20;
            currRow++;
            row0.CreateCell(0).SetCellValue("运行准备部分");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 6));

            for (int i = 0; i < ListYXZB.Count; i++)
            {
                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListYXZB[i]["Code"].ToString());
                row0.CreateCell(1).SetCellValue(ListYXZB[i]["EquipName"].ToString());
                row0.CreateCell(2).SetCellValue(ListYXZB[i]["Specification"].ToString());
                row0.CreateCell(3).SetCellValue(ListYXZB[i]["Unit"].ToString());
                row0.CreateCell(4).SetCellValue(ListYXZB[i]["Quantity"].ToString());
                row0.CreateCell(5).SetCellValue(ListYXZB[i]["QuoteMoney"].ToString());
                row0.CreateCell(6).SetCellValue(ListYXZB[i]["Sum"].ToString());
                tempSum += double.Parse(ListYXZB[i]["Sum"].ToString());
            }
            //小计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("小计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue(tempSum.ToString("F2"));
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
            Sum += tempSum;
            tempSum = 0;


            //合计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("合计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue(Sum.ToString("F2"));
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
            //刷新各行格式
            ICell commCell;
            for (int i = 0; i < currRow; i++)
            {
                //Sheet1.GetRow(i).Height = 60 * 20;
                for (int j = 0; j < 7; j++)
                {
                    commCell = Sheet1.GetRow(i).GetCell(j);
                    //CommCellStyle(book, commCell);
                    commCell.CellStyle = fCellStyle;

                }
            }

            return Sheet1;
        }


        public ISheet EstUserTestSheet(string PreEstId, ISheet Sheet1, XSSFCellStyle fCellStyle)
        {
            string sql = "";
            //YXZB
            sql = "SELECT TestName,Quantity,QuoteMoney,Quantity*QuoteMoney as Sum,Memo " +
               "FROM o_PETestFee where IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程前期测算' ORDER BY TestName";
            var ListTest = dao.GetList(sql, new string[] { "TestName", "Quantity", "QuoteMoney", "Sum", "Memo" });
            //清零操作
            for (int i = 0; i < ListTest.Count; i++)
            {
                if (ListTest[i]["Sum"].ToString() == "")
                {
                    ListTest[i]["Sum"] = 0;
                }
            }
            Sheet1.SetColumnWidth(0, 5000);
            Sheet1.SetColumnWidth(1, 3000);
            Sheet1.SetColumnWidth(2, 3000);
            Sheet1.SetColumnWidth(3, 4000);
            Sheet1.SetColumnWidth(4, 5000);


            double Sum = 0;
            double tempSum = 0;
            int currRow = 0;
            bool tagTitle = true;//表面建立表头
            IRow rowSum;
            string Type = "";
            //设置表头
            IRow row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("试验费名称");
            row0.CreateCell(1).SetCellValue("工程量");
            row0.CreateCell(2).SetCellValue("单价");
            row0.CreateCell(3).SetCellValue("合价");
            row0.CreateCell(4).SetCellValue("备注");
            row0.Height = 20 * 20;

            ////////////////////////////////////////////////////
            for (int i = 0; i < ListTest.Count; i++)
            {
                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListTest[i]["TestName"].ToString());
                row0.CreateCell(1).SetCellValue(ListTest[i]["Quantity"].ToString());
                row0.CreateCell(2).SetCellValue(ListTest[i]["QuoteMoney"].ToString());
                row0.CreateCell(3).SetCellValue(ListTest[i]["Sum"].ToString());
                row0.CreateCell(4).SetCellValue(ListTest[i]["Memo"].ToString());
                tempSum += double.Parse(ListTest[i]["Sum"].ToString());
            }
            //合计计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("合计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue(tempSum.ToString("F2"));
            row0.CreateCell(4).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 2));


            //刷新各行格式
            ICell commCell;
            for (int i = 0; i < currRow; i++)
            {
                //Sheet1.GetRow(i).Height = 60 * 20;
                for (int j = 0; j < 5; j++)
                {
                    commCell = Sheet1.GetRow(i).GetCell(j);
                    //CommCellStyle(book, commCell);
                    commCell.CellStyle = fCellStyle;

                }
            }

            return Sheet1;
        }

        public ISheet EstUserCoordFeeSheet(string PreEstId, ISheet Sheet1, XSSFCellStyle fCellStyle)
        {
            string sql = "";
            //YXZB
            sql = "SELECT CooContent,CooDepart,Quantity,QuoteMoney,Quantity*QuoteMoney as Sum,Memo " +
               "FROM o_PECoordFee where IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程前期测算' ORDER BY CooContent";
            var ListCoordFee = dao.GetList(sql, new string[] { "CooContent", "CooDepart", "Quantity", "QuoteMoney", "Sum", "Memo" });
            //清零操作
            for (int i = 0; i < ListCoordFee.Count; i++)
            {
                if (ListCoordFee[i]["Sum"].ToString() == "")
                {
                    ListCoordFee[i]["Sum"] = 0;
                }
            }
            Sheet1.SetColumnWidth(0, 4000);
            Sheet1.SetColumnWidth(1, 6000);
            Sheet1.SetColumnWidth(2, 3000);
            Sheet1.SetColumnWidth(3, 3000);
            Sheet1.SetColumnWidth(4, 3000);
            Sheet1.SetColumnWidth(5, 5000);


            double Sum = 0;
            double tempSum = 0;
            int currRow = 0;
            //设置表头
            IRow row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("协调内容");
            row0.CreateCell(1).SetCellValue("协调部门");
            row0.CreateCell(2).SetCellValue("数量");
            row0.CreateCell(3).SetCellValue("单价");
            row0.CreateCell(4).SetCellValue("合价");
            row0.CreateCell(5).SetCellValue("备注");
            row0.Height = 20 * 20;

            ////////////////////////////////////////////////////
            for (int i = 0; i < ListCoordFee.Count; i++)
            {
                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListCoordFee[i]["CooContent"].ToString());
                row0.CreateCell(1).SetCellValue(ListCoordFee[i]["CooDepart"].ToString());
                row0.CreateCell(2).SetCellValue(ListCoordFee[i]["Quantity"].ToString());
                row0.CreateCell(3).SetCellValue(ListCoordFee[i]["QuoteMoney"].ToString());
                row0.CreateCell(4).SetCellValue(ListCoordFee[i]["Sum"].ToString());
                row0.CreateCell(5).SetCellValue(ListCoordFee[i]["Memo"].ToString());
                tempSum += double.Parse(ListCoordFee[i]["Sum"].ToString());
            }
            //合计计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("合计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue(tempSum.ToString("F2"));
            row0.CreateCell(5).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 2));


            //刷新各行格式
            ICell commCell;
            for (int i = 0; i < currRow; i++)
            {
                //Sheet1.GetRow(i).Height = 60 * 20;
                for (int j = 0; j < 6; j++)
                {
                    commCell = Sheet1.GetRow(i).GetCell(j);
                    //CommCellStyle(book, commCell);
                    commCell.CellStyle = fCellStyle;

                }
            }

            return Sheet1;
        }


        public ISheet EstUserCivilSheet(string PreEstId, ISheet Sheet1, XSSFCellStyle fCellStyle)
        {
            string sql = "";
            //WX
            sql = "SELECT Name,Unit,UnitPrice,Quantity,Quantity*UnitPrice as Sum,Memo " +
               "FROM o_PECivilPrice where TabType='WX' and IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程前期测算' ORDER BY PECivilPriceId desc";
            var ListWX = dao.GetList(sql, new string[] { "Name", "Unit", "UnitPrice", "Quantity", "Sum", "Memo" });
            //清零操作
            for (int i = 0; i < ListWX.Count; i++)
            {
                if (ListWX[i]["Sum"].ToString() == "")
                {
                    ListWX[i]["Sum"] = 0;
                }
            }
            //HXN
            sql = "SELECT Name,Unit,UnitPrice,Quantity,Quantity*UnitPrice as Sum,Memo " +
               "FROM o_PECivilPrice where TabType='HXN' and IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程前期测算' ORDER BY PECivilPriceId desc";
            var ListHXN = dao.GetList(sql, new string[] { "Name", "Unit", "UnitPrice", "Quantity", "Sum", "Memo" });
            //清零操作
            for (int i = 0; i < ListHXN.Count; i++)
            {
                if (ListHXN[i]["Sum"].ToString() == "")
                {
                    ListHXN[i]["Sum"] = 0;
                }
            }


            Sheet1.SetColumnWidth(0, 6000);
            Sheet1.SetColumnWidth(1, 2000);
            Sheet1.SetColumnWidth(2, 2000);
            Sheet1.SetColumnWidth(3, 3000);
            Sheet1.SetColumnWidth(4, 3000);
            Sheet1.SetColumnWidth(5, 5000);


            double Sum = 0;
            double tempSum = 0;
            int currRow = 0;
            //设置表头
            IRow row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("项目名称");
            row0.CreateCell(1).SetCellValue("单位");
            row0.CreateCell(2).SetCellValue("工程量");
            row0.CreateCell(3).SetCellValue("单价");
            row0.CreateCell(4).SetCellValue("合价");
            row0.CreateCell(5).SetCellValue("备注");
            row0.Height = 20 * 20;
            /////////////////////////////////////////////////////////////////////
            //WX
            row0 = Sheet1.CreateRow(currRow);   //创建行   
            row0.Height = 25 * 20;
            currRow++;
            row0.CreateCell(0).SetCellValue("外线部分");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));

            for (int i = 0; i < ListWX.Count; i++)
            {
                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListWX[i]["Name"].ToString());
                row0.CreateCell(1).SetCellValue(ListWX[i]["Unit"].ToString());
                row0.CreateCell(2).SetCellValue(ListWX[i]["Quantity"].ToString());
                row0.CreateCell(3).SetCellValue(ListWX[i]["UnitPrice"].ToString());
                row0.CreateCell(4).SetCellValue(ListWX[i]["Sum"].ToString());
                row0.CreateCell(5).SetCellValue(ListWX[i]["Memo"].ToString());
                tempSum += double.Parse(ListWX[i]["Sum"].ToString());
            }
            /////////////////////////////////////////////////////////////////////
            //HXN
            row0 = Sheet1.CreateRow(currRow);   //创建行   
            row0.Height = 25 * 20;
            currRow++;
            row0.CreateCell(0).SetCellValue("外线部分");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));

            for (int i = 0; i < ListHXN.Count; i++)
            {
                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListHXN[i]["Name"].ToString());
                row0.CreateCell(1).SetCellValue(ListHXN[i]["Unit"].ToString());
                row0.CreateCell(2).SetCellValue(ListHXN[i]["Quantity"].ToString());
                row0.CreateCell(3).SetCellValue(ListHXN[i]["UnitPrice"].ToString());
                row0.CreateCell(4).SetCellValue(ListHXN[i]["Sum"].ToString());
                row0.CreateCell(5).SetCellValue(ListHXN[i]["Memo"].ToString());
                tempSum += double.Parse(ListHXN[i]["Sum"].ToString());
            }

            ///////////////////////////////////////////////////////////////////////////////
            //合计
            row0 = Sheet1.CreateRow(currRow);   //创建行   
            row0.Height = 25 * 20;
            currRow++;
            row0.CreateCell(0).SetCellValue("外线部分");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue(tempSum.ToString("F2"));
            row0.CreateCell(5).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 3));

            //刷新各行格式
            ICell commCell;
            for (int i = 0; i < currRow; i++)
            {
                //Sheet1.GetRow(i).Height = 60 * 20;
                for (int j = 0; j < 6; j++)
                {
                    commCell = Sheet1.GetRow(i).GetCell(j);
                    //CommCellStyle(book, commCell);
                    commCell.CellStyle = fCellStyle;

                }
            }





            return Sheet1;



        }


        public ISheet EstUserCivilSheetDetail(string PreEstId, ISheet Sheet1, XSSFCellStyle fCellStyle, XSSFWorkbook workbook)
        {
            string sql = "";
            string temp = "";
            int i1 = 0;
            int currRow = 0;
            int tempRow = 0;


            XSSFCellStyle fCellStyle2 = (XSSFCellStyle)workbook.CreateCellStyle();
            XSSFFont ffont = (XSSFFont)workbook.CreateFont();
            ffont.FontHeight = 15 * 20;
            ffont.FontName = "宋体";
            ffont.Color = new XSSFColor(Color.Black).Indexed;
            fCellStyle2.SetFont(ffont);
            fCellStyle2.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直对齐
            fCellStyle2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平对齐


            //下面先获取PECivilPriceId
            sql = "select PECivilPriceId from o_PECivilPrice where IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程前期测算'";

            var PECivilIdList = dao.GetList(sql, new string[] { "PECivilPriceId" });

            for (int ii = 0; ii < PECivilIdList.Count; ii++)
            {

                sql = "select * from o_PECivilPrice where PECivilPriceId=" + PECivilIdList[ii]["PECivilPriceId"].ToString();
                var Map1 = dao.GetList(sql, new string[] { "PECivilPriceId", "Name", "TaxRate", "Unit", "UnitPrice", "Memo" })[0];
                sql = "select XMMC,GCLJSS,DW,GCL,RGDJ,GCL*RGDJ as RGHJ,CLDJ,GCL*CLDJ as CLHJ,GCL*(RGDJ+CLDJ) AS ZJ,Memo from o_PECivilPriceSingle where PECivilPriceId=" + PECivilIdList[ii]["PECivilPriceId"].ToString() + " order by id asc";
                var List1 = dao.GetList(sql, new string[] { "XMMC", "GCLJSS", "DW", "GCL", "RGDJ", "RGHJ", "CLDJ", "CLHJ", "ZJ", "Memo" });

                IRow row0 = Sheet1.CreateRow(currRow);   //创建行
                currRow++;
                ICell cell = row0.CreateCell(0);                     //创建列

                temp = Map1["Name"].ToString();
                cell.SetCellValue(temp);
                cell.CellStyle = fCellStyle2;
                Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 10));
                //创建表头
                string[] firstName = { "序号", "项目名称", "工程量计算式", "单位", "工程量", "人工费", "", "材料费", "", "总价", "备注" };
                string[] firstName2 = { "", "", "", "", "", "单价", "合价", "单价", "合价", "", "" };

                //表头宽度
                int[] widths = { 0, 6, 10, 2, 2, 2, 3, 2, 3, 3, 5 };//长度要和标题的长度一样 0代表默认

                //设置表头
                IRow row1 = Sheet1.CreateRow(currRow);
                currRow++;
                IRow row2 = Sheet1.CreateRow(currRow);
                currRow++;

                row1.Height = 600;
                row2.Height = 600;
                for (i1 = 0; i1 < firstName.Length; i1++)
                {
                    row1.CreateCell(i1).SetCellValue(firstName[i1]);
                    row2.CreateCell(i1).SetCellValue(firstName2[i1]);
                    if (widths[i1] == 0)
                    {
                        Sheet1.SetColumnWidth(i1, 1000);
                    }
                    else
                    {
                        Sheet1.SetColumnWidth(i1, widths[i1] * 1000);
                    }

                }
                Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 2, currRow - 1, 0, 0));
                Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 2, currRow - 1, 1, 1));
                Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 2, currRow - 1, 2, 2));
                Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 2, currRow - 1, 3, 3));
                Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 2, currRow - 1, 4, 4));
                Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 2, currRow - 2, 5, 6));
                Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 2, currRow - 2, 7, 8));
                Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 2, currRow - 1, 9, 9));
                Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 2, currRow - 1, 10, 10));

                //刷新表头格式
                ICell commCell;
                for (int i = 0; i < 2; i++)
                {
                    for (int j = 0; j < 11; j++)
                    {
                        commCell = Sheet1.GetRow(i + currRow - 2).GetCell(j);
                        //CommCellStyle(book, commCell);
                        commCell.CellStyle = fCellStyle;
                    }
                }


                double taxSum = 0;
                double RgSum = 0;
                double ClSum = 0;
                tempRow = currRow;
                for (int i = 0; i < List1.Count; i++)
                {
                    IRow temprow = Sheet1.CreateRow(currRow);
                    currRow++;
                    temprow.Height = 450;
                    temprow.CreateCell(0).SetCellValue(i + 1);
                    temprow.CreateCell(1).SetCellValue(List1[i]["XMMC"].ToString());
                    temprow.CreateCell(2).SetCellValue(List1[i]["GCLJSS"].ToString());
                    temprow.CreateCell(3).SetCellValue(List1[i]["DW"].ToString());
                    temprow.CreateCell(4).SetCellValue(double.Parse(double.Parse(List1[i]["GCL"].ToString()).ToString("F2")));
                    temprow.CreateCell(5).SetCellValue(double.Parse(double.Parse(List1[i]["RGDJ"].ToString()).ToString("F2")));
                    temprow.CreateCell(6).SetCellValue(double.Parse(double.Parse(List1[i]["RGHJ"].ToString()).ToString("F2")));
                    RgSum += double.Parse(List1[i]["RGHJ"].ToString());
                    temprow.CreateCell(7).SetCellValue(double.Parse(double.Parse(List1[i]["CLDJ"].ToString()).ToString("F2")));
                    temprow.CreateCell(8).SetCellValue(double.Parse(double.Parse(List1[i]["CLHJ"].ToString()).ToString("F2")));
                    ClSum += double.Parse(List1[i]["CLHJ"].ToString());
                    temprow.CreateCell(9).SetCellValue(double.Parse(double.Parse(List1[i]["ZJ"].ToString()).ToString("F2")));
                    temprow.CreateCell(10).SetCellValue(List1[i]["Memo"].ToString());
                    taxSum += double.Parse(double.Parse(List1[i]["ZJ"].ToString()).ToString("F2"));
                }
                for (int i = 0; i < List1.Count; i++)
                {
                    for (int j = 1; j < 11; j++)
                    {
                        commCell = Sheet1.GetRow(i + tempRow).GetCell(j);
                        //CommCellStyle(book, commCell);
                        commCell.CellStyle = fCellStyle;
                    }
                }
                //添加税金行
                IRow row3 = Sheet1.CreateRow(currRow);
                currRow++;
                row3.Height = 450;
                row3.CreateCell(0).SetCellValue(List1.Count + 1);
                row3.CreateCell(1).SetCellValue("税金");
                row3.CreateCell(2).SetCellValue("");
                row3.CreateCell(3).SetCellValue("元");
                row3.CreateCell(4).SetCellValue(double.Parse(taxSum.ToString("F2")));
                row3.CreateCell(5).SetCellValue(double.Parse(Map1["TaxRate"].ToString()).ToString("P"));

                double temptax = double.Parse(Map1["TaxRate"].ToString());
                row3.CreateCell(6).SetCellValue(double.Parse((taxSum * temptax).ToString("F2")));
                row3.CreateCell(7).SetCellValue("");
                row3.CreateCell(8).SetCellValue("");
                row3.CreateCell(9).SetCellValue(double.Parse((taxSum * temptax).ToString("F2")));
                row3.CreateCell(10).SetCellValue("");
                for (int i = 1; i < 11; i++)
                {
                    commCell = Sheet1.GetRow(currRow - 1).GetCell(i);
                    //CommCellStyle(book, commCell);
                    commCell.CellStyle = fCellStyle;
                }
                //增加合计
                IRow row4 = Sheet1.CreateRow(currRow);
                currRow++;
                row4.Height = 450;
                row4.CreateCell(0).SetCellValue("合计");
                row4.CreateCell(1).SetCellValue("");
                row4.CreateCell(2).SetCellValue("");
                row4.CreateCell(3).SetCellValue("");
                row4.CreateCell(4).SetCellValue("");
                row4.CreateCell(5).SetCellValue("");
                row4.CreateCell(6).SetCellValue(RgSum);
                row4.CreateCell(7).SetCellValue("");
                row4.CreateCell(8).SetCellValue(ClSum);
                row4.CreateCell(9).SetCellValue(double.Parse((RgSum + ClSum + taxSum * temptax).ToString("F2")));
                row4.CreateCell(10).SetCellValue("");

                Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
                for (int i = 0; i < 11; i++)
                {
                    commCell = Sheet1.GetRow(currRow - 1).GetCell(i);
                    //CommCellStyle(book, commCell);
                    commCell.CellStyle = fCellStyle;
                }

            }






            return Sheet1;
        }




        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="sheet">要合并单元格所在的sheet</param>
        /// <param name="rowstart">开始行的索引</param>
        /// <param name="rowend">结束行的索引</param>
        /// <param name="colstart">开始列的索引</param>
        /// <param name="colend">结束列的索引</param>
        public void SetCellRangeAddress(NPOI.SS.UserModel.ISheet sheet, int rowstart, int rowend, int colstart, int colend)
        {
            CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
            sheet.AddMergedRegion(cellRangeAddress);
        }

        /// <summary>
        /// 设置标题单元格样式
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="cell"></param>
        public void setCellStyle(XSSFWorkbook workbook, ICell cell)
        {
            XSSFCellStyle fCellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
            XSSFFont ffont = (XSSFFont)workbook.CreateFont();
            ffont.FontHeight = 15 * 20;
            ffont.FontName = "宋体";
            ffont.Color = new XSSFColor(Color.Black).Indexed;
            fCellStyle.SetFont(ffont);
            fCellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直对齐
            fCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平对齐

            //fCellStyle.BorderLeft = BorderStyle.Thin;
            //fCellStyle.BorderRight = BorderStyle.Thin;
            //fCellStyle.BorderTop = BorderStyle.Thin;
            //fCellStyle.BorderBottom = BorderStyle.Thin;
            cell.CellStyle = fCellStyle;
        }


        public void CommCellStyle(XSSFWorkbook workbook, ICell cell)
        {
            XSSFCellStyle fCellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
            XSSFFont ffont = (XSSFFont)workbook.CreateFont();
            ffont.FontHeight = 220;
            ffont.FontName = "宋体";
            ffont.Color = new XSSFColor(Color.Black).Indexed;
            fCellStyle.SetFont(ffont);
            fCellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直对齐
            fCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平对齐
            IDataFormat format = workbook.CreateDataFormat();
            fCellStyle.DataFormat = format.GetFormat("#,##0.00 ");//这是设置为3位数货币样式

            fCellStyle.BorderLeft = BorderStyle.Thin;
            fCellStyle.BorderRight = BorderStyle.Thin;
            fCellStyle.BorderTop = BorderStyle.Thin;
            fCellStyle.BorderBottom = BorderStyle.Thin;
            cell.CellStyle = fCellStyle;
        }



    }
}